############################################################################################
#                            Fonctions de gestion des Adhérents                            #
############################################################################################

# Requête retournant l'id d'un adhérent ayant un login donné
sub getIdSubscriber{
  my $hashBureau = $dbd->selectall_arrayref("SELECT idAdherent FROM adherent WHERE login = '$_[0]';");
  return $$hashBureau[0][0];
}

# Requête retournant l'id de la dernière personne ajoutée
sub selectPerson{
  my $hashPerson = $dbd->selectall_arrayref("SELECT MAX(idPersonne) FROM personne WHERE nomPersonne = '$_[0]' AND prenomPersonne = '$_[1]';");
  return $$hashPerson[0][0];
}

# Requête permettant d'ajouter une personne
sub addPerson{
  my $id = selectPerson($_[0],$_[1]);
  if (defined($id)){
    return $id;
  }
  my $prep = $dbd->prepare("INSERT INTO personne (nomPersonne,prenomPersonne) VALUES ('$_[0]','$_[1]');");
  $prep->execute;
  $id=$dbd->{q{mysql_insertid}};
  $prep->finish;
  return $id;
}

# Requête permettant d'ajouter un élève
sub addStudent{#(nom,prenom,promotion,filiere,login)
  my $id = addPerson($_[0],$_[1]);
  my $prep = $dbd->prepare("INSERT INTO eleve (promotion,filiere, login, idPersonne) VALUES ('$_[2]','$_[3]','$_[4]','$id');");
  $prep->execute;
  $prep->finish;
}

# Requête permettant d'ajouter un adhérent
sub addSubscriber{#(nom,prenom,promotion,filiere,login,dateAdhesion)
  my @quotedArgs;
  my $i=0;
  foreach my $arg (@_) {
    $quotedArgs[$i++]=$dbd->quote($arg);
  }

  addStudent($_[0],$_[1],$_[2],$_[3],$_[4]);
  my $req = $dbd->prepare("INSERT INTO adherent (dateAdhesion,login) VALUES (str_to_date('$_[5]','%d/%m/%Y'),'$_[4]');");
  $req->execute;
  my $id = $dbd->{q{mysql_insertid}};
  $req->finish;
  return $id;
}

# Requête retournant la liste des notes d'un adhérent
sub listOfMark{
  my $hashGames = $dbd->selectall_hashref("SELECT * from note WHERE idAdherent = '$_[0]'", 'nomJeu');
  return $hashGames;
}

# Requête retournant la liste des commentaires d'un adhérent
sub listOfCommit{
  my $hashGames = $dbd->selectall_hashref("SELECT * from commente,evenement WHERE commente.idAdherent = '$_[0]' AND commente.idEvenement = evenement.idEvenement", 'idEvenement');
  return $hashGames;
}

# Requête permettant de supprimer un adhérent
sub deleteMember{#(nom,prenom,promotion,filiere,login,dateAdhesion)
  my $req = <<"SQL";
    DELETE FROM adherent, eleve, personne 
       USING adherent, eleve, personne 
       WHERE adherent.login = '$_[0]' 
       AND adherent.login = eleve.login 
       AND eleve.idPersonne = personne.idPersonne;
SQL
  my $prep = $dbd->prepare($req);
  $prep->execute;
  $prep->finish;
}

# Requête permettant de rechercher des adhérents
sub searchMember { #(nom,prenom,promotion,filiere,login,dateAdhesion)
  my @quotedArgs;
  my $i=0;
  foreach my $arg (@_) {
    $quotedArgs[$i++]=quotemeta($arg);
  }
    
  my $date = convertDate($_[5]) . '%';
  my $req;
  if($_[6] == 1){
  $req = <<"SQL";
    SELECT * from adherent, eleve, personne, bureau
        where adherent.login = eleve.login
        and eleve.idPersonne = personne.idPersonne
        and bureau.idAdherent = adherent.idAdherent
        and CURDATE() < dateSortie
        and personne.nomPersonne like '%$quotedArgs[0]%'
        and personne.prenomPersonne like '%$quotedArgs[1]%'
        and eleve.promotion like '$quotedArgs[2]%'
        and eleve.filiere like '$quotedArgs[3]%'
        and eleve.login like '%$quotedArgs[4]%'
        and adherent.dateAdhesion like '$date'
        order by personne.nomPersonne;
SQL
  }
  elsif($_[6] == 0){
  $req = <<"SQL";
    SELECT * from adherent, eleve, personne
        where adherent.login = eleve.login
        and eleve.idPersonne = personne.idPersonne
        and adherent.idAdherent not in (SELECT idAdherent FROM bureau WHERE CURDATE() < dateSortie)
        and personne.nomPersonne like '%$quotedArgs[0]%'
        and personne.prenomPersonne like '%$quotedArgs[1]%'
        and eleve.promotion like '$quotedArgs[2]%'
        and eleve.filiere like '$quotedArgs[3]%'
        and eleve.login like '%$quotedArgs[4]%'
        and adherent.dateAdhesion like '$date'
        order by personne.nomPersonne;
SQL
  }
  else{
      $req = <<"SQL";
    SELECT * from adherent, eleve, personne
        where adherent.login = eleve.login
        and eleve.idPersonne = personne.idPersonne
        and personne.nomPersonne like '%$quotedArgs[0]%'
        and personne.prenomPersonne like '%$quotedArgs[1]%'
        and eleve.promotion like '$quotedArgs[2]%'
        and eleve.filiere like '$quotedArgs[3]%'
        and eleve.login like '%$quotedArgs[4]%'
        and adherent.dateAdhesion like '$date'
        order by personne.nomPersonne;
SQL
  }
  my $hashMembers = $dbd->selectall_hashref($req, 'login');
  return $hashMembers;
}

# Requête permettant de mettre à jour les informations d'un adhérent donné
sub updateMember { #(nom,prenom,promotion,filiere,login,dateAdhesion)
  my @quotedArgs;
  my $i=0;
  foreach my $arg (@_) {
    $quotedArgs[$i++]=quotemeta($arg);
  }
  
  my $date = convertDate($_[5]);
  my $req = <<"SQL";
    UPDATE personne,eleve,adherent 
        SET nomPersonne = '$quotedArgs[0]', 
            prenomPersonne = '$quotedArgs[1]', 
            promotion = '$quotedArgs[2]', 
            filiere = '$quotedArgs[3]', 
            dateAdhesion = '$date'
        WHERE adherent.login = '$quotedArgs[4]'
          and eleve.login = adherent.login
          and eleve.idPersonne = personne.idPersonne;
SQL
  my $prep = $dbd->prepare($req);
  $prep->execute;
  $prep->finish;
}

# Requête retournant la liste des emprunts d'un adhérent
sub listBorrowBook{
  my $req;
  if($_[1] ne ""){
    $req = <<"SQL";
    SELECT * from livre, emprunt, personne, ecriture
        WHERE emprunt.ISBN = '$_[1]'
          AND emprunt.idAdherent = '$_[0]'
          AND livre.ISBN = emprunt.ISBN
          AND ecriture.ISBN = livre.ISBN
          AND personne.idPersonne = ecriture.idPersonne;
SQL
  }
  else{
    $req = <<"SQL";
    SELECT * from livre, emprunt, personne, ecriture
        WHERE emprunt.idAdherent = '$_[0]'
          AND livre.ISBN = emprunt.ISBN
          AND ecriture.ISBN = livre.ISBN
          AND personne.idPersonne = ecriture.idPersonne;
SQL
  }
  my $hashBooks = $dbd->selectall_hashref($req, 'ISBN');
  return $hashBooks;
}

1;
